const express = require('express');
const router = express.Router();
const { getKnex } = require('../db/knex');
const Result = require('../utils/Result');
const { updateProductImage } = require('../service/productImageService')

// 查询所有作品（支持分页、筛选）
router.get('/', async (req, res) => {
  try {
    const knex = getKnex();
    const { page = 1, pageSize = 10, category, techStack } = req.query;

    // ✅ 先查 total，只查 projects
    const totalQuery = knex('projects').count({ count: '*' });

    if (category) {
      totalQuery.where('category_id', category);
    }

    if (techStack) {
      totalQuery.whereIn('id', function () {
        this.select('project_id')
          .from('project_technologies')
          .where('technology_id', techStack);
      });
    }

    const totalObj = await totalQuery.first();
    const total = totalObj?.count || 0;

    // ✅ 再查数据，使用子查询聚合 tech_stack_ids 和 images
    let listQuery = knex('projects').select(
      'projects.*',
      knex.raw(`(
        select JSON_ARRAYAGG(technology_id)
        from project_technologies
        where project_id = projects.id
      ) as tech_stack_ids`),
      knex.raw(`(
        select JSON_ARRAYAGG(image_url)
        from project_images
        where project_id = projects.id
      ) as images`),
      knex.raw(`(
        select JSON_ARRAYAGG(technology_id)
        from project_platforms
        where project_id = projects.id
      ) as platform_ids`)
    );

    if (category) {
      listQuery.where('projects.category_id', category);
    }

    if (techStack) {
      listQuery.whereIn('projects.id', function () {
        this.select('project_id')
          .from('project_technologies')
          .where('technology_id', techStack);
      });
    }

    const list = await listQuery
      .orderBy('projects.created_at', 'desc')
      .offset((page - 1) * pageSize)
      .limit(Number(pageSize));

    res.json(Result.success({ total, list }));
  } catch (error) {
    console.error(error);
    res.json(Result.fail('查询作品失败'));
  }
});


// 根据id获取作品详情（含技术栈和截图）
router.get('/:id', async (req, res) => {
  try {
    const knex = getKnex();
    const id = req.params.id;

    const project = await knex('projects').where({ id }).first();
    if (!project) return res.json(Result.fail('作品不存在'));

    // 获取技术栈标签
    const techStacks = await knex('technologies')
      .join('project_technologies', 'technologies.id', 'project_technologies.technology_id')
      .where('project_technologies.project_id', id)
      .select('technologies.id', 'technologies.name');

    // 获取作品截图
    const images = await knex('project_images').where('project_id', id).orderBy('order_index', 'asc');

    res.json(Result.success({ ...project, techStacks, images }));
  } catch (error) {
    console.error(error);
    res.json(Result.fail('获取作品详情失败'));
  }
});

// 新增作品
router.post('/', async (req, res) => {
  try {
    const knex = getKnex();
    const {
      title,
      description,
      price = 0.0,
      category_id, // 应该是 category_id
      main_image,
      github_url,
      baidu_url,
      tech_stack_ids = [], // 技术栈id数组
      platform_ids = [], // 平台id数组
      images = [], // 图片数组
    } = req.body;

    // 插入作品
    const [id] = await knex('projects').insert({
      title,
      description,
      price,
      category_id,
      main_image,
      github_url,
      baidu_url,
      created_at: new Date(),
      updated_at: new Date(),
    });

    // 插入关联技术栈
    if (tech_stack_ids.length > 0) {
      const relations = tech_stack_ids.map(tid => ({ project_id: id, technology_id: tid }));
      await knex('project_technologies').insert(relations);
    }

    // 插入关联平台
    if (platform_ids.length > 0) {
      const platformRelations = platform_ids.map(pid => ({ project_id: id, technology_id: pid }));
      await knex('project_platforms').insert(platformRelations);
    }

    if (images.length > 0) {
      const relations = images.map((image, index) => ({ project_id: id, image_url: image, order_index: index }));
      await knex('project_images').insert(relations);
    }

    res.json(Result.success({ id }, '作品创建成功'));
  } catch (error) {
    console.error(error);
    res.json(Result.fail('作品创建失败'));
  }
});

// 更新作品
router.put('/:id', async (req, res) => {
  try {
    const knex = getKnex();
    const id = req.params.id;
    const {
      title,
      description,
      price,
      category,
      main_image,
      github_url,
      baidu_url,
      tech_stack_ids = [],
      images = [],
      platform_ids=[]
    } = req.body;

    const updateData = {
      title,
      description,
      price,
      category_id: category,
      main_image,
      github_url,
      baidu_url,
      updated_at: new Date(),
    };

    await knex('projects').where({ id }).update(updateData);

    // 先删除旧的技术栈关联
    await knex('project_technologies').where('project_id', id).del();

    // 插入新的关联
    if (tech_stack_ids.length > 0) {
      const relations = tech_stack_ids.map(tid => ({ project_id: id, technology_id: tid }));
      await knex('project_technologies').insert(relations);
    }

    // 删除关联平台
    await knex('project_platforms').where('project_id', id).del();
    // 插入新的平台关联
    if (platform_ids.length > 0) {
      const platformRelations = platform_ids.map(pid => ({ project_id: id, technology_id: pid }));
      await knex('project_platforms').insert(platformRelations);
    }

    // 删除旧的图片
    await knex('project_images').where('project_id', id).del();
    // 插入新的图片
    if (images.length > 0) {
      const relations = images.map((image, index) => ({ project_id: id, image_url: image, order_index: index }));
      await knex('project_images').insert(relations);
    }

    await updateProductImage(knex, id, images);

    res.json(Result.success(null, '作品更新成功'));
  } catch (error) {
    console.error(error);
    res.json(Result.fail('作品更新失败'));
  }
});

// 删除作品（连带关联表）
router.delete('/:id', async (req, res) => {
  try {
    const knex = getKnex();
    const id = req.params.id;

    await knex.transaction(async trx => {
      await trx('project_technologies').where('project_id', id).del();
      await trx('project_images').where('project_id', id).del();
      await trx('projects').where('id', id).del();
    });

    res.json(Result.success(null, '作品删除成功'));
  } catch (error) {
    console.error(error);
    res.json(Result.fail('作品删除失败'));
  }
});

module.exports = router;
